﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;
using System.Configuration;


namespace PMS.DAL
{
    public class ReferenceDAL
    {
        #region Common
        private static ReferenceENT CreateReferenceFromReader(IDataReader reader)
        {
            ReferenceENT item = new ReferenceENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.ReferenceID.ToString()))) item.ReferenceID = Utility.IntParse(reader[ReferenceColumns.ReferenceID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.FileName.ToString()))) item.FileName = Utility.StringParse(reader[ReferenceColumns.FileName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.FilePath.ToString()))) item.FilePath = Utility.StringParse(reader[ReferenceColumns.FilePath.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.FileSize.ToString()))) item.FileSize = Utility.DecimalParse(reader[ReferenceColumns.FileSize.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.ProjectID.ToString()))) item.ProjectID = Utility.IntParse(reader[ReferenceColumns.ProjectID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.OwnerID.ToString()))) item.OwnerID = Utility.IntParse(reader[ReferenceColumns.OwnerID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[ReferenceColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[ReferenceColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[ReferenceColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[ReferenceColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[ReferenceColumns.Isdeleted.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ReferenceColumns.IsHide.ToString()))) item.IsHide = Utility.IntParse(reader[ReferenceColumns.IsHide.ToString()]);
 
            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static ReferenceENT GetReferenceByID(int referenceID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceGetByID.ToString());
                database.AddInParameter(dbCommand, "@ReferenceID", DbType.Int32, referenceID);

                ReferenceENT reference = new ReferenceENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        reference = CreateReferenceFromReader(reader);
                    }
                    reader.Close();
                }

                return reference;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetReferenceByID");
            }
        }

        public static ReferenceCollection GetReferenceList( int projectID, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceGetList.ToString());
                ReferenceCollection listReference = new ReferenceCollection();
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listReference.Add(CreateReferenceFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listReference;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetReferenceList");
            }
        }

        public static ReferenceCollection GetReferenceList(string referenceID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceGetListForDownLoad.ToString());
                ReferenceCollection listReference = new ReferenceCollection();
                database.AddInParameter(dbCommand, "@ReferenceID", DbType.String, referenceID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listReference.Add(CreateReferenceFromReader(reader));
                    }
                    reader.Close();
                }


                return listReference;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetReferenceList");
            }
        }


        public static ReferenceCollection GetReferenceListByStatementID(int statementID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceGetListByStatementID.ToString());
                ReferenceCollection listReference = new ReferenceCollection();
                database.AddInParameter(dbCommand, "@StatementID", DbType.String, statementID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listReference.Add(CreateReferenceFromReader(reader));
                    }
                    reader.Close();
                }


                return listReference;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetReferenceList");
            }
        }

        public static ReferenceCollection GetReferenceListForCMB(int userID, int projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);

                ReferenceCollection listReference = new ReferenceCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listReference.Add(CreateReferenceFromReader(reader));
                    }
                    reader.Close();
                }

                return listReference;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetReferenceListForCMB");
            }
        }

        #endregion

        #region Insert, Update, Delete

        public static int InsertReference(ReferenceENT reference)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceInsert.ToString());
                int referenceID = 0;
                database.AddOutParameter(dbCommand, "@ReferenceID", DbType.Int32, reference.ReferenceID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, reference.OwnerID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, reference.ProjectID);
                database.AddInParameter(dbCommand, "@FileName", DbType.String, reference.FileName);
                database.AddInParameter(dbCommand, "@FileSize", DbType.Decimal, reference.FileSize);
                database.AddInParameter(dbCommand, "@FilePath", DbType.String, reference.FilePath);
                database.AddInParameter(dbCommand, "@IsHide", DbType.Int32, reference.IsHide);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, reference.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, reference.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, reference.Isdeleted);

                database.ExecuteNonQuery(dbCommand);
                referenceID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ReferenceID"));
                return referenceID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertReference");
            }
        }
        
        public static int UpdateReference(ReferenceENT reference)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceUpdate.ToString());

                database.AddOutParameter(dbCommand, "@ReferenceID", DbType.Int32, reference.ReferenceID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, reference.OwnerID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, reference.ProjectID);
                database.AddInParameter(dbCommand, "@FileName", DbType.String, reference.FileName);
                database.AddInParameter(dbCommand, "@FileSize", DbType.Decimal, reference.FileSize);
                database.AddInParameter(dbCommand, "@IsHide", DbType.Int32, reference.IsHide);
                database.AddInParameter(dbCommand, "@FilePath", DbType.String, reference.FilePath);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, reference.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, reference.Isdeleted);

                database.ExecuteNonQuery(dbCommand);

                int referenceID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ReferenceID"));
                return referenceID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertReference");
            }
        }
        
        public static int DeleteReference(string referenceID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceDelete.ToString());
                database.AddInParameter(dbCommand, "@ReferenceID", DbType.String, referenceID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteReference");
            }
        }

        public static int ShowReference(string referenceID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceShow.ToString());
                database.AddInParameter(dbCommand, "@ReferenceID", DbType.String, referenceID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_ShowReference");
            }
        }
        public static int HideReference(string referenceID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procReferenceHide.ToString());
                database.AddInParameter(dbCommand, "@ReferenceID", DbType.String, referenceID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_HideReference");
            }
        }



        #endregion

    }
}
